USE ChinaDW;
GO
CREATE TABLE ChinaDW.ODS.DBM_ACT_DLAUNCH_GAME_RECORD (
	id nvarchar(64) NOT NULL,
	h5_access_id nvarchar(64) NOT NULL,
	activity_id nvarchar(64) NOT NULL,
	activity_name nvarchar(256) NULL,
	user_id nvarchar(32) NOT NULL,
	user_name nvarchar(256) NULL,
	game_no nvarchar(64) NOT NULL,
	score BIGINT NULL,
	time_length FLOAT NULL,
	product_id nvarchar(32) NULL,
	product_name nvarchar(256) NULL,
	create_time datetime2 NOT NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2022-01-11>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_game_record 
-- 初始化ODS.DBM_ACT_DLAUNCH_GAME_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD_INITIALIZE 
(@sourceCount INT OUTPUT, @deleteCount INT OUTPUT, @insertCount INT OUTPUT, @updateCount INT OUTPUT)
AS BEGIN 
	SELECT @sourceCount = COUNT(1) FROM ODS.DBM_ACT_DLAUNCH_GAME_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
		TRUNCATE TABLE ODS.DBM_ACT_DLAUNCH_GAME_RECORD
		INSERT INTO ODS.DBM_ACT_DLAUNCH_GAME_RECORD(
			id, h5_access_id, activity_id, activity_name, user_id, user_name, game_no, score, time_length, product_id, product_name, create_time
		)
		SELECT 
			id, h5_access_id, activity_id, activity_name, user_id, user_name, game_no, score, time_length, product_id, product_name, create_time
		FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_game_record;')
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2022-01-11>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_game_record 
-- 的近两日数据，并更新至 ODS.DBM_ACT_DLAUNCH_GAME_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD
 (@sourceCount INT OUTPUT, @deleteCount INT OUTPUT, @insertCount INT OUTPUT, @updateCount INT OUTPUT)
AS BEGIN 
	SELECT @sourceCount = COUNT(1) FROM ODS.DBM_ACT_DLAUNCH_GAME_RECORD;
	IF @sourceCount > 0
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRAN myTran;
		MERGE INTO ODS.DBM_ACT_DLAUNCH_GAME_RECORD target
			USING (
			SELECT *
			FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_game_record where create_time > date_add(now(),interval -2 DAY);')) sources
			ON (target.id collate SQL_Latin1_General_CP1_CI_AS = sources.id collate SQL_Latin1_General_CP1_CI_AS)
			WHEN MATCHED THEN 
				UPDATE 
				SET target.h5_access_id=sources.h5_access_id,
					target.activity_id=sources.activity_id,
					target.activity_name=sources.activity_name,
					target.user_id=sources.user_id,
					target.user_name=sources.user_name,
					target.game_no=sources.game_no,
					target.score=sources.score,
					target.time_length=sources.time_length,
					target.product_id=sources.product_id,
					target.product_name=sources.product_name,
					target.create_time=sources.create_time
			WHEN NOT MATCHED THEN 
				INSERT(	id, h5_access_id, activity_id, activity_name, user_id, user_name, game_no, score, time_length, product_id, product_name, create_time)
				VALUES( id, h5_access_id, activity_id, activity_name, user_id, user_name, game_no, score, time_length, product_id, product_name, create_time);
		COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
	END;
END;
GO
INSERT INTO ODS.DW_SP_INFO(ProcedureName, Operation, TargetTable, SourceTableView, Project) VALUES
('ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD_INITIALIZE', 'TRUNCATE+INSERT', 'ODS.DBM_ACT_DLAUNCH_GAME_RECORD', 'ChinaDBM.ms009_actmgmt.act_dlaunch_game_record', 'DLaunch'),
('ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD', 'TRUNCATE+INSERT', 'ODS.DBM_ACT_DLAUNCH_GAME_RECORD', 'ChinaDBM.ms009_actmgmt.act_dlaunch_game_record', 'DLaunch');

EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD_INITIALIZE';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD';
GO
--! Updte ODS.EXCEL_DLAUNCH_LIST --manual update
--! DW_C_DLaunch_2021221更新
UPDATE ChinaDW.ODS.EXCEL_DLAUNCH_LIST SET ActivityTTLUnquiePges = 11 WHERE ActivityID = 'AC1632297733013';
UPDATE ChinaDW.ODS.EXCEL_DLAUNCH_LIST SET ActivityTTLUnquiePges = 35 WHERE ActivityID = 'AC1637312715059';
INSERT INTO ODS.EXCEL_DLAUNCH_LIST(ActivityName, ActivityID, ActivityType, ActivityStatus, ActivityTTLUnquiePges, DataSource, StartTime, ProductID, ProductName)
VALUES('血流动力学监测','AC1636337726529','2','1','32','DBM','2021-11-11 21:51:46.744',null,'围术期')
--! DW_C_DLaunch_2021221 Change STG.DBM_DLAUNCH_VISIT_RECORD data format due to DBM datatype changing
ALTER TABLE ChinaDW.STG.DBM_DLAUNCH_VISIT_RECORD ALTER COLUMN TimeSpand FLOAT;
ALTER TABLE ChinaDW.DW.FACT_DLAUNCH_VISIT_RECORD ALTER COLUMN TimeSpand FLOAT;

--! DW_C_DLaunch_2021221 Change STG.DBM_DLAUNCH_VISIT_RECORD data format due to DBM datatype changing
ALTER TABLE ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER ALTER COLUMN PageCode VARCHAR(50);


--! Insert ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER --manual update
--! DW_C_DLaunch_2021221更新
/* -- quick tool for batch insert excel
DROP table if exists #TEMP;
SELECT * INTO #TEMP FROM (
select ROW_NUMBER() over(order by ActivityID,DataSource, PageCode) RN,*
from ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
WHERE ActivityID IN ('AC1632297733013','AC1637312715059')
)A order by ActivityID, DataType, DataSource, PageCode;

DECLARE @i INT = 0, @RNs INT = (SELECT count(*) from #TEMP ),
@prints VARCHAR(2000)
;
while @i < @RNs BEGIN
	SET @i = @i + 1;
	SELECT @prints = concat('(','''', ActivityID,'''',',','''',DataType ,'''',
		',','''',DataSource,'''',',','''',PageCode,'''',',1','),')
	FROM #TEMP WHERE RN = @i;
	PRINT (@prints);
END;
*/
INSERT INTO ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
(ActivityID, DataType, DataSource, PageCode, TAG) VALUES
('test33013','ACCESS','DBM','1',1),
('test33013','QA2','DBM','1',1),
('test33013','QA1','DBM','1',1),
('test33013','QA2','DBM','2',1),
('test33013','ACCESS','DBM','2',1),
('test33013','ACCESS','DBM','3',1),
('test33013','QA2','DBM','3',1),
('test33013','ACCESS','DBM','5',1),
('test33013','ACCESS','DBM','6',1),
('test33013','ACCESS','DBM','8',1),
('test33013','ACCESS','DBM','9',1),
('test15059','ACCESS','DBM','1',1),
('test15059','QA2','DBM','1',1),
('test15059','PAGE','DBM','1',1),
('test15059','PAGE','DBM','10',1),
('test15059','PAGE','DBM','11',1),
('test15059','PAGE','DBM','12',1),
('test15059','QA2','DBM','2',1),
('test15059','PAGE','DBM','2',1),
('test15059','ACCESS','DBM','2',1),
('test15059','QA2','DBM','3',1),
('test15059','ACCESS','DBM','3',1),
('test15059','PAGE','DBM','3',1),
('test15059','PAGE','DBM','4',1),
('test15059','QA2','DBM','4',1),
('test15059','ACCESS','DBM','4',1),
('test15059','QA2','DBM','5',1),
('test15059','ACCESS','DBM','5',1),
('test15059','PAGE','DBM','5',1),
('test15059','QA2','DBM','6',1),
('test15059','ACCESS','DBM','6',1),
('test15059','PAGE','DBM','6',1),
('test15059','PAGE','DBM','7',1),
('test15059','ACCESS','DBM','7',1),
('test15059','PAGE','DBM','8',1),
('test15059','PAGE','DBM','9',1),
('test15059','VIDEO','DBM','v1307200832001',1),
('test15059','PAGE','DBM','v1307200832001',1),
('test15059','VIDEO','DBM','v1307200832002',1),
('test15059','PAGE','DBM','v1307200832002',1),
('test15059','PAGE','DBM','v1307200832003',1),
('test15059','VIDEO','DBM','v1307200832003',1),
('test15059','VIDEO','DBM','v1307200832004',1),
('test15059','PAGE','DBM','v1307200832004',1),
('test15059','PAGE','DBM','v1307200832005',1),
('test15059','VIDEO','DBM','v1307200832005',1),
('test15059','GAME','DBM','12',1),
('test15059','GAME','DBM','10',1),
('test15059','GAME','DBM','11',1),
('test15059','GAME','DBM','9',1),
('test15059','GAME','DBM','8',1),
('test26529','QA2','DBM','7',1),
('test26529','ACCESS','DBM',	'21',1),
('test26529','ACCESS','DBM','3',1),
('test26529','QA2','DBM','4',1),
('test26529','ACCESS','DBM','0',1),
('test26529','ACCESS','DBM','24',1),
('test26529','ACCESS','DBM','6',1),
('test26529','QA1','DBM','3',1),
('test26529','ACCESS','DBM','1',1),
('test26529','ACCESS','DBM','12',1),
('test26529','QA2','DBM','5',1),
('test26529','ACCESS','DBM','13',1),
('test26529','ACCESS','DBM','5',1),
('test26529','ACCESS','DBM','16',1),
('test26529','ACCESS','DBM','17',1),
('test26529','ACCESS','DBM','19',1),
('test26529','ACCESS','DBM','2',1),
('test26529','ACCESS','DBM','18',1),
('test26529','QA2','DBM','6',1),
('test26529','ACCESS','DBM','15',1),
('test26529','ACCESS','DBM','20',1),
('test26529','ACCESS','DBM','9',1),
('test26529','ACCESS','DBM','22',1),
('test26529','ACCESS','DBM','7',1),
('test26529','QA1','DBM','1',1),
('test26529','ACCESS','DBM','14',1),
('test26529','ACCESS','DBM','23',1),
('test26529','ACCESS','DBM','11',1),
('test26529','ACCESS','DBM','4',1),
('test26529','ACCESS','DBM','10',1),
('test26529','ACCESS','DBM','8',1),
('test26529','QA1','DBM','2',1)
;
update 
ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
set TAG = 0
where ActivityID = 'AC1637312715059'
and DataType in ('ACCESS','GAME','VIDEO','QA2')
;
UPDATE 
ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
set TAG = 0
where ActivityID = 'AC1632297733013'
and DataType = 'ACCESS' AND PageCode IN ('5','6')
;
UPDATE ChinaDW.ODS.EXCEL_DLAUNCH_LIST SET ActivityTTLUnquiePges = 5 WHERE ActivityID = 'AC1637312715059';
UPDATE ChinaDW.ODS.EXCEL_DLAUNCH_LIST SET ActivityTTLUnquiePges = 9 WHERE ActivityID = 'AC1632297733013';
GO
-- 将 Access_record / answer_record / page_record / vedio_reocrd 合并为一张表
-- 便于后续统计页面数(问题回答数量，视频观看数量，页码阅读数量等 （现阶段仅对独立页面数量进行计数，）)
--! DW_C_DLaunch_2021221 修改Video表中时间字段，强转为Float型数据 video211221
ALTER VIEW STG.VW_T_DBM_DLAUNCH_RECORD as
SELECT 	Activity_Id ActivityID, Activity_Name ActivityName, User_Id UserId, User_Name UserName, 
		H5_Access_Id H5AccessId,  page_Code PageCode, DataType, 
		 Time_Spand TimeSpand, Login_Date LoginDate, 
		 cast(Create_Date as datetime) CreateDate ,
		 'DBM' DataSource -- 区分数据来源
FROM (
SELECT 	activity_id, activity_name, user_id, user_name, h5_access_id, page_code, DataType, time_spand,
		login_date, create_date
FROM (
		SELECT 	activity_id, activity_name , user_id , user_name , h5_access_id , 
				page_code, 
				'PAGE' DataType,5 time_spand, --  需要统一统计口径
				null login_date, create_time create_date
		FROM ods.DBM_ACT_DLAUNCH_PAGE_RECORD ddpr --  替换数据库和表名
		INNER join ods.EXCEL_DLAUNCH_LIST edl1 on edl1.ActivityID = ddpr.activity_id
	UNION all
		SELECT 
				activity_id, activity_name , user_id , user_name , h5_access_id ,
				page_code, DataType,  sum(time_spand) time_spand, -- 需要统一统计口径
				null login_date ,MIN(create_time) create_date
		FROM (
			SELECT 	activity_id, activity_name, user_id, user_name, h5_access_id, 
					question_code page_code, 
					case question_category when 1 then 'QA1' when 2 then 'QA2'
					else 'QA0' end as DataType, 0 time_spand, --  需要统一统计口径
					create_time
			FROM ods.DBM_ACT_DLAUNCH_ANSWER_RECORD dadar  --  替换数据库和表名
			INNER join ods.EXCEL_DLAUNCH_LIST edl2 on edl2.ActivityID = dadar.activity_id
		) QA1 
		group by activity_id, activity_name , user_id , user_name,
				h5_access_id , page_code, DataType
	UNION ALL
		--! DW_C_DLaunch_2021221
		SELECT activity_id, activity_name , user_id , user_name , h5_access_id , 
			game_no page_code,'GAME' DataType,TimeSpand,NULL LoginDate, CreateDate 
		FROM (	select 	activity_id, activity_name , user_id , user_name , h5_access_id , 
						game_no ,SUM(time_length) TimeSpand, MIN(cast(create_time as datetime)) CreateDate 
				from ODS.DBM_ACT_DLAUNCH_GAME_RECORD
				GROUP BY activity_id, activity_name , user_id , user_name , h5_access_id , game_no
		) gameinner
		INNER join ods.EXCEL_DLAUNCH_LIST edl3 on edl3.ActivityID = gameinner.activity_id
		--! DW_C_DLaunch_2021221
	UNION all
		SELECT 	activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType, sum(time_spand) time_spand,
				min(login_datatime) login_datatime, min(create_datetime) create_datetime
		FROM (
			SELECT 	activity_id, activity_name, user_id , user_name, h5_access_id, 
					page_code ,'ACCESS' DataType, 5 time_spand,
					-- 数据源中存在24时的数据，将其替换为00
					cast(cast(login_data as date) as datetime) + 
					cast(cast(
					case when len(login_time) < 7 then login_time -- sql server中，时间类型会自动补0例如 2:8:7则会被转为 02:08:07
					else 
					case when left(login_time,2) = '24' then '00' + right(replace(login_time,' ',''),6) -- 时间标注为24时报错，替换为00  需要核实
					else login_time end
					end as time) as datetime) login_datatime ,
					create_time create_datetime
			FROM ods.DBM_ACT_DLAUNCH_ACCESS_RECORD adar 
			INNER join ods.EXCEL_DLAUNCH_LIST edl3 on edl3.ActivityID = adar.activity_id
			where year( login_data) > 2000 -- 数据源中存在 0011和 0010年的数据
		) acc1 group by activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType
) record1
UNION all
SELECT 
	activity_id, activity_name, user_id, user_name, h5_access_id,
	video_code page_code, 'VIDEO' DataType,
	--!--------由于DBM数据类型发生变化，导致DW中数据类型转换出现错误------DW_C_DLaunch_2021221-----------
	cast(view_length as FLOAT) timespand , 
	--!--------由于DBM数据类型发生变化，导致DW中数据类型转换出现错误------DW_C_DLaunch_2021221-----------
	null login_date, create_time 
FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD dadvr --  替换数据库和表名
INNER join ods.EXCEL_DLAUNCH_LIST edl4 on edl4.ActivityID = dadvr.activity_id
) union_data
UNION ALL 
SELECT ActivityId, ActivityName, userId, UserName, H5AccessId, PageCode, DataType,
	TimeSpand, LoginDate, CreateDate,'ManualInput' DATASOURCE FROM ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT --  看是否需要加
;
GO

